"""
数据一致性校验工具
用于检查和修复ProjectTask和WorkHour表之间的数据不一致问题
"""

from datetime import datetime, timedelta
from app import app, db, ProjectTask, WorkHour


def check_data_consistency():
    """
    检查数据一致性问题
    返回不一致的记录列表
    """
    inconsistencies = []
    
    with app.app_context():
        # 1. 检查ProjectTask.work_hour与WorkHour记录总和是否一致
        tasks = ProjectTask.query.all()
        for task in tasks:
            # 计算该任务的所有工时记录总和
            total_hours = db.session.query(db.func.sum(WorkHour.work_hours)).filter(
                WorkHour.task_id == task.id
            ).scalar() or 0
            
            task_work_hour = task.work_hour or 0
            
            # 如果不一致，记录问题
            if abs(total_hours - task_work_hour) > 0.01:  # 允许小数点误差
                inconsistencies.append({
                    'type': 'task_work_hour_mismatch',
                    'task_id': task.id,
                    'task_name': task.name,
                    'task_work_hour': task_work_hour,
                    'actual_total_hours': total_hours,
                    'difference': total_hours - task_work_hour
                })
        
        # 2. 检查任务状态与进度是否一致
        for task in tasks:
            status_progress_mismatch = False
            
            # 检查状态与进度的一致性
            if task.status == '未开始' and task.progress > 0:
                status_progress_mismatch = True
            elif task.status == '已完成' and task.progress < 100:
                status_progress_mismatch = True
            elif task.status == '进行中' and (task.progress == 0 or task.progress == 100):
                status_progress_mismatch = True
            
            if status_progress_mismatch:
                inconsistencies.append({
                    'type': 'status_progress_mismatch',
                    'task_id': task.id,
                    'task_name': task.name,
                    'status': task.status,
                    'progress': task.progress
                })
        
        # 3. 检查任务时间逻辑是否合理
        for task in tasks:
            if task.start_date and task.end_date and task.start_date > task.end_date:
                inconsistencies.append({
                    'type': 'invalid_date_range',
                    'task_id': task.id,
                    'task_name': task.name,
                    'start_date': task.start_date.strftime('%Y-%m-%d'),
                    'end_date': task.end_date.strftime('%Y-%m-%d')
                })
        
        # 4. 检查工时记录是否有异常值
        work_hours = WorkHour.query.all()
        for wh in work_hours:
            # 检查工时是否为负数或超过24小时
            if wh.work_hours <= 0 or wh.work_hours > 24:
                inconsistencies.append({
                    'type': 'invalid_work_hours',
                    'work_hour_id': wh.id,
                    'task_id': wh.task_id,
                    'work_hours': wh.work_hours,
                    'work_date': wh.work_date.strftime('%Y-%m-%d')
                })
            
            # 检查工时日期是否在未来
            if wh.work_date > datetime.now().date():
                inconsistencies.append({
                    'type': 'future_work_date',
                    'work_hour_id': wh.id,
                    'task_id': wh.task_id,
                    'work_date': wh.work_date.strftime('%Y-%m-%d')
                })
        
        # 5. 检查孤立记录（任务已删除但工时记录仍存在）
        orphaned_work_hours = db.session.query(WorkHour).outerjoin(
            ProjectTask, WorkHour.task_id == ProjectTask.id
        ).filter(ProjectTask.id.is_(None)).all()
        
        for wh in orphaned_work_hours:
            inconsistencies.append({
                'type': 'orphaned_work_hour',
                'work_hour_id': wh.id,
                'task_id': wh.task_id,
                'work_hours': wh.work_hours,
                'work_date': wh.work_date.strftime('%Y-%m-%d')
            })
    
    return inconsistencies


def fix_data_consistency(inconsistencies, auto_fix=False):
    """
    修复数据一致性问题
    :param inconsistencies: 不一致问题列表
    :param auto_fix: 是否自动修复可以安全修复的问题
    :return: 修复结果报告
    """
    fix_report = {
        'fixed': [],
        'manual_review': [],
        'errors': []
    }
    
    with app.app_context():
        for issue in inconsistencies:
            try:
                if issue['type'] == 'task_work_hour_mismatch':
                    if auto_fix:
                        # 自动修复：更新任务的工时为实际工时总和
                        task = ProjectTask.query.get(issue['task_id'])
                        if task:
                            task.work_hour = issue['actual_total_hours']
                            db.session.commit()
                            fix_report['fixed'].append(f"已修复任务 {task.name} 的工时，从 {issue['task_work_hour']} 更新为 {issue['actual_total_hours']}")
                    else:
                        fix_report['manual_review'].append(f"任务 {issue['task_name']} 工时不一致: 任务记录 {issue['task_work_hour']}, 实际总和 {issue['actual_total_hours']}")
                
                elif issue['type'] == 'status_progress_mismatch':
                    if auto_fix:
                        # 自动修复：根据进度更新状态
                        task = ProjectTask.query.get(issue['task_id'])
                        if task:
                            old_status = task.status
                            if task.progress == 0:
                                task.status = '未开始'
                            elif task.progress == 100:
                                task.status = '已完成'
                            else:
                                task.status = '进行中'
                            
                            db.session.commit()
                            fix_report['fixed'].append(f"已修复任务 {task.name} 的状态，从 {old_status} 更新为 {task.status}")
                    else:
                        fix_report['manual_review'].append(f"任务 {issue['task_name']} 状态与进度不匹配: 状态 {issue['status']}, 进度 {issue['progress']}%")
                
                elif issue['type'] == 'invalid_date_range':
                    fix_report['manual_review'].append(f"任务 {issue['task_name']} 日期范围无效: 开始日期 {issue['start_date']} 晚于结束日期 {issue['end_date']}")
                
                elif issue['type'] == 'invalid_work_hours':
                    if auto_fix and (issue['work_hours'] <= 0 or issue['work_hours'] > 24):
                        # 自动修复：将异常工时设置为1小时
                        wh = WorkHour.query.get(issue['work_hour_id'])
                        if wh:
                            old_hours = wh.work_hours
                            wh.work_hours = 1.0
                            db.session.commit()
                            fix_report['fixed'].append(f"已修复工时记录 {issue['work_hour_id']}，从 {old_hours} 更新为 1.0")
                    else:
                        fix_report['manual_review'].append(f"工时记录 {issue['work_hour_id']} 有异常值: {issue['work_hours']} 小时")
                
                elif issue['type'] == 'future_work_date':
                    fix_report['manual_review'].append(f"工时记录 {issue['work_hour_id']} 日期在未来: {issue['work_date']}")
                
                elif issue['type'] == 'orphaned_work_hour':
                    if auto_fix:
                        # 自动修复：删除孤立记录
                        wh = WorkHour.query.get(issue['work_hour_id'])
                        if wh:
                            db.session.delete(wh)
                            db.session.commit()
                            fix_report['fixed'].append(f"已删除孤立工时记录 {issue['work_hour_id']}")
                    else:
                        fix_report['manual_review'].append(f"发现孤立工时记录 {issue['work_hour_id']}，关联任务 {issue['task_id']} 不存在")
                
            except Exception as e:
                fix_report['errors'].append(f"修复问题 {issue['type']} 时出错: {str(e)}")
    
    return fix_report


def generate_consistency_report():
    """
    生成数据一致性报告
    """
    inconsistencies = check_data_consistency()
    
    report = {
        'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'total_inconsistencies': len(inconsistencies),
        'summary': {},
        'details': inconsistencies
    }
    
    # 按类型统计问题
    type_counts = {}
    for issue in inconsistencies:
        issue_type = issue['type']
        type_counts[issue_type] = type_counts.get(issue_type, 0) + 1
    
    report['summary'] = type_counts
    
    return report


if __name__ == '__main__':
    # 运行数据一致性检查
    print("开始数据一致性检查...")
    inconsistencies = check_data_consistency()
    
    if not inconsistencies:
        print("未发现数据一致性问题。")
    else:
        print(f"发现 {len(inconsistencies)} 个数据一致性问题:")
        for issue in inconsistencies:
            print(f"- {issue['type']}: {issue}")
        
        # 询问是否自动修复
        auto_fix = input("\n是否自动修复可以安全修复的问题？(y/n): ").lower() == 'y'
        
        if auto_fix:
            print("\n开始修复数据一致性问题...")
            fix_report = fix_data_consistency(inconsistencies, auto_fix=True)
            
            print(f"\n修复完成:")
            print(f"- 自动修复: {len(fix_report['fixed'])} 个问题")
            for item in fix_report['fixed']:
                print(f"  * {item}")
            
            print(f"- 需要手动检查: {len(fix_report['manual_review'])} 个问题")
            for item in fix_report['manual_review']:
                print(f"  * {item}")
            
            if fix_report['errors']:
                print(f"- 修复错误: {len(fix_report['errors'])} 个问题")
                for item in fix_report['errors']:
                    print(f"  * {item}")
        
        # 生成报告
        report = generate_consistency_report()
        print(f"\n数据一致性报告已生成，共 {report['total_inconsistencies']} 个问题。")